﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Globalization;
using System.Linq;
using System.Web;
using bdxyit.com.RecruitmentAppSys.IDAL;
using System.Data.SqlClient;
using bdxyit.com.RecruitmentAppSys.DBUtility;
using System.Data;
using bdxyit.com.RecruitmentAppSys.Models;


/***
*机器名：BDXYIT-HUI
*项目名称：$projectname$
*命名空间：bdxyit.com.RecruitmentAppSys.SQLDAL
*文件名：UserInfoModels
*系统时间：3/25/2012 3:39:19 PM
*当前用户：huihui
*创建时间：2012
*
*修改时间：2012 3/25/2012 3:39:19 PM
*修改事件：
***/

namespace bdxyit.com.RecruitmentAppSys.SQLDAL
{
    public class UserInfoDAL : IUserInfoDAL
    {
        private const string SQL_SELECT_USERINFO_BY_USERID = " select  top 1 UserID,UserName,UserName2,Sex,Hometown,EthnicID,PoliticalID,Height,Weight,VisionID,Marital,Birthday,StartWorkTime,Job,SectorPosts,Location,PersonnelFiles,PhoneNumber,Email,Address,PostalCode,Training,Performance,PhotoAddress,Other from UserInfos "
                                                            + " where UserID=@UserID ";
        private const string SQL_DELETE_USERINFO_BY_USERID = " delete from UserInfos where UserID=@UserID ";

        private const string SQL_UPDATE_USERINFO_BY_USERID = "  update UserInfos set "
                                                            + " UserName=@UserName,"
                                                            + " UserName2=@UserName2,"
                                                            + " Sex=@Sex,"
                                                            + " Hometown=@Hometown,"
                                                            + " EthnicID=@EthnicID,"
                                                            + " PoliticalID=@PoliticalID,"
                                                            + " Height=@Height,"
                                                            + " Weight=@Weight,"
                                                            + " VisionID=@VisionID,"
                                                            + " Marital=@Marital,"
                                                            + " Birthday=@Birthday,"
                                                            + " StartWorkTime=@StartWorkTime,"
                                                            + " Job=@Job,"
                                                            + " SectorPosts=@SectorPosts,"
                                                            + " Location=@Location,"
                                                            + " PersonnelFiles=@PersonnelFiles,"
                                                            + " PhoneNumber=@PhoneNumber,"
                                                            + " Email=@Email,"
                                                            + " Address=@Address,"
                                                            + " PostalCode=@PostalCode,"
                                                            + " Training=@Training,"
                                                            + " Performance=@Performance,"
                                                            + " PhotoAddress=@PhotoAddress,"
                                                            + " Other=@Other"
                                                            + " where "
                                                            + " UserID=@UserID";

        private const string SQL_INSERT_USERINFO = " insert into UserInfos( UserID,UserName,UserName2,Sex,Hometown,EthnicID,PoliticalID,Height,Weight,VisionID,Marital,Birthday,StartWorkTime,Job,SectorPosts,Location,PersonnelFiles,PhoneNumber,Email,Address,PostalCode,Training,Performance,PhotoAddress,Other) "
                                                   + " values " +
                                                   " ( @UserID,@UserName,@UserName2,@Sex,@Hometown,@EthnicID,@PoliticalID,@Height,@Weight,@VisionID,@Marital,@Birthday,@StartWorkTime,@Job,@SectorPosts,@Location,@PersonnelFiles,@PhoneNumber,@Email,@Address,@PostalCode,@Training,@Performance,@PhotoAddress,@Other)";
        private const string FIELD_USERID = "@UserID";
        private const string FIELD_USERNAME = "@UserName";
        private const string FIELD_USERNAME2 = "@UserName2";
        private const string FIELD_SEX = "@Sex";
        private const string FIELD_HOMETOWN = "@Hometown";
        private const string FIELD_ETHNICID = "@EthnicID";
        private const string FIELD_POLITICALID = "@PoliticalID";
        private const string FIELD_HEIGHT = "@Height";
        private const string FIELD_WEIGHT = "@Weight";
        private const string FIELD_LEFTVISIONID = "@LeftVisionID";
        private const string FIELD_RIGHTVISIONID = "@RightVisionID";
        private const string FIELD_MARITAL = "@Marital";
        private const string FIELD_BIRTHDAY = "@Birthday";
        private const string FIELD_STARTWORKTIME = "@StartWorkTime";
        private const string FIELD_JOB = "@Job";
        private const string FIELD_SECTORPOSTS = "@SectorPosts";
        private const string FIELD_LOCALTION = "@Location";
        private const string FIELD_PERSONNEFILES = "@PersonnelFiles";
        private const string FIELD_PHONENUMBER = "@PhoneNumber";
        private const string FIELD_EMAIL = "@Email";
        private const string FIELD_ADRESS = "@Address";
        private const string FIELD_POSTALCODE = "@PostalCode";
        private const string FIELD_TRAINING = "@Training";
        private const string FIELD_PERFORMANCE = "@Performance";
        private const string FIELD_PHOTOADRESS = "@PhotoAddress";
        private const string FIELD_OTHER = "@Other";

        /// <summary>
        /// 获取用户基本信息
        /// </summary>
        /// <param name="UserId">用户ID</param>
        /// <returns></returns>
        public UserInfo SelectUserInfoByUserId(string UserId)
        {
            UserInfo userInfo = null;
            //创建参数
            SqlParameter parm = new SqlParameter(FIELD_USERID, SqlDbType.VarChar, 50);
            parm.Value = UserId;

            //执行读取家庭成员
            using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocal, CommandType.Text, SQL_SELECT_USERINFO_BY_USERID, parm))
            {
                if (rdr.Read())
                {
                    userInfo = new UserInfo();
                    if (rdr.HasRows == true)
                    {
                        if (rdr["UserID"] != null && rdr["UserID"].ToString() != "")
                        {
                            userInfo.UserID = rdr["UserID"].ToString();
                        }
                        if (rdr["UserName"] != null && rdr["UserName"].ToString() != "")
                        {
                            userInfo.UserName = rdr["UserName"].ToString();
                        }
                        if (rdr["UserName2"] != null && rdr["UserName2"].ToString() != "")
                        {
                            userInfo.UserName2 = rdr["UserName2"].ToString();
                        }
                        if (rdr["Sex"] != null && rdr["Sex"].ToString() != "")
                        {
                            userInfo.Sex = rdr["Sex"].ToString();
                        }
                        if (rdr["Hometown"] != null && rdr["Hometown"].ToString() != "")
                        {
                            userInfo.Hometown = rdr["Hometown"].ToString();
                        }
                        if (rdr["EthnicID"] != null && rdr["EthnicID"].ToString() != "")
                        {
                            userInfo.EthnicID = rdr["EthnicID"].ToString();
                        }
                        if (rdr["PoliticalID"] != null && rdr["PoliticalID"].ToString() != "")
                        {
                            userInfo.PoliticalID = rdr["PoliticalID"].ToString();
                        }
                        if (rdr["Height"] != null && rdr["Height"].ToString() != "")
                        {
                            userInfo.Height = rdr["Height"].ToString();
                        }
                        if (rdr["Weight"] != null && rdr["Weight"].ToString() != "")
                        {
                            userInfo.Weight = rdr["Weight"].ToString();
                        }
                     
                        if (rdr["Marital"] != null && rdr["Marital"].ToString() != "")
                        {
                            userInfo.Marital =rdr["Marital"].ToString();
                        }
                        if (rdr["Birthday"] != null && rdr["Birthday"].ToString() != "")
                        {
                            userInfo.Birthday = rdr["Birthday"].ToString();
                        }
                        if (rdr["StartWorkTime"] != null && rdr["StartWorkTime"].ToString() != "")
                        {
                            userInfo.StartWorkTime = DateTime.Parse(rdr["StartWorkTime"].ToString());
                        }
                        if (rdr["Job"] != null && rdr["Job"].ToString() != "")
                        {
                            userInfo.Job = rdr["Job"].ToString();
                        }
                        if (rdr["SectorPosts"] != null && rdr["SectorPosts"].ToString() != "")
                        {
                            userInfo.SectorPosts = rdr["SectorPosts"].ToString();
                        }
                        if (rdr["Location"] != null && rdr["Location"].ToString() != "")
                        {
                            userInfo.Location = rdr["Location"].ToString();
                        }
                        if (rdr["PersonnelFiles"] != null && rdr["PersonnelFiles"].ToString() != "")
                        {
                            userInfo.PersonnelFiles = rdr["PersonnelFiles"].ToString();
                        }
                        if (rdr["PhoneNumber"] != null && rdr["PhoneNumber"].ToString() != "")
                        {
                            userInfo.PhoneNumber =rdr["PhoneNumber"].ToString();
                        }
                        if (rdr["Email"] != null && rdr["Email"].ToString() != "")
                        {
                            userInfo.Email = rdr["Email"].ToString();
                        }
                        if (rdr["Address"] != null && rdr["Address"].ToString() != "")
                        {
                            userInfo.Address = rdr["Address"].ToString();
                        }
                        if (rdr["PostalCode"] != null && rdr["PostalCode"].ToString() != "")
                        {
                            userInfo.PostalCode =rdr["PostalCode"].ToString();
                        }
                        if (rdr["Training"] != null && rdr["Training"].ToString() != "")
                        {
                            userInfo.Training = rdr["Training"].ToString();
                        }
                        if (rdr["Performance"] != null && rdr["Performance"].ToString() != "")
                        {
                            userInfo.Performance = rdr["Performance"].ToString();
                        }
                        if (rdr["PhotoAddress"] != null && rdr["PhotoAddress"].ToString() != "")
                        {
                            userInfo.PhotoAddress = rdr["PhotoAddress"].ToString();
                        }
                        if (rdr["Other"] != null && rdr["Other"].ToString() != "")
                        {
                            userInfo.Other = rdr["Other"].ToString();
                        }

                    }
                }
            }
            return userInfo;
        }

        /// <summary>
        /// 移除一个用户
        /// </summary>
        /// <param name="UserId"></param>
        /// <returns></returns>
        public bool DeleteUserInfoByUserId(string UserId)
        {
            bool flag = false;
            //创建参数
            SqlParameter parm = new SqlParameter(FIELD_USERID, SqlDbType.VarChar, 18);
            parm.Value = UserId;
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_DELETE_USERINFO_BY_USERID;
                cmd.Parameters.Add(parm);
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }

        /// <summary>
        /// 插入一个用户
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public bool InsertUserInfo(UserInfo user)
        {
            bool flag = false;

            SqlParameter[] UserInfoParms = GetUserInfoParameters();
            SqlCommand cmd = new SqlCommand();
            UserInfoParms[0].Value = user.UserID;
            UserInfoParms[1].Value = user.UserName;
            UserInfoParms[2].Value = user.UserName2;
            UserInfoParms[3].Value = user.Sex;
            UserInfoParms[4].Value = user.Hometown;
            UserInfoParms[5].Value = user.EthnicID;
            UserInfoParms[6].Value = user.PoliticalID;
            UserInfoParms[7].Value = user.Height;
            UserInfoParms[8].Value = user.Weight;
            UserInfoParms[9].Value = user.LeftVisionID;
            UserInfoParms[10].Value = user.RightVisionID;
            UserInfoParms[10].Value = user.Marital;
            UserInfoParms[11].Value = user.Birthday;
            UserInfoParms[12].Value = user.StartWorkTime;
            UserInfoParms[13].Value = user.Job;
            UserInfoParms[14].Value = user.SectorPosts;
            UserInfoParms[15].Value = user.Location;
            UserInfoParms[16].Value = user.PersonnelFiles;
            UserInfoParms[17].Value = user.PhoneNumber;

            UserInfoParms[18].Value = user.Email;
            UserInfoParms[19].Value = user.Address;
            UserInfoParms[20].Value = user.PostalCode;
            UserInfoParms[21].Value = user.SectorPosts;
            UserInfoParms[22].Value = user.Training;
            UserInfoParms[23].Value = user.Performance;
            UserInfoParms[24].Value = user.PhotoAddress;
            UserInfoParms[25].Value = user.Other;

            foreach (SqlParameter parm in UserInfoParms)
                cmd.Parameters.Add(parm);

            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_INSERT_USERINFO;
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }

        /// <summary>
        /// 更新一个用户
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public bool UpdateUserInfo(UserInfo user)
        {
            bool flag = false;
            SqlParameter[] UserInfoParms = GetUserInfoParameters();
            SqlCommand cmd = new SqlCommand();
            UserInfoParms[0].Value = user.UserID;
            UserInfoParms[1].Value = user.UserName;
            UserInfoParms[2].Value = user.UserName2;
            UserInfoParms[3].Value = user.Sex;
            UserInfoParms[4].Value = user.Hometown;
            UserInfoParms[5].Value = user.EthnicID;
            UserInfoParms[6].Value = user.PoliticalID;
            UserInfoParms[7].Value = user.Height;
            UserInfoParms[8].Value = user.Weight;
            UserInfoParms[9].Value = user.LeftVisionID;
            UserInfoParms[10].Value = user.RightVisionID;
            UserInfoParms[10].Value = user.Marital;
            UserInfoParms[11].Value = user.Birthday;
            UserInfoParms[12].Value = user.StartWorkTime;
            UserInfoParms[13].Value = user.Job;
            UserInfoParms[14].Value = user.SectorPosts;
            UserInfoParms[15].Value = user.Location;
            UserInfoParms[16].Value = user.PersonnelFiles;
            UserInfoParms[17].Value = user.PhoneNumber;

            UserInfoParms[18].Value = user.Email;
            UserInfoParms[19].Value = user.Address;
            UserInfoParms[20].Value = user.PostalCode;
            UserInfoParms[21].Value = user.SectorPosts;
            UserInfoParms[22].Value = user.Training;
            UserInfoParms[23].Value = user.Performance;
            UserInfoParms[24].Value = user.PhotoAddress;
            UserInfoParms[25].Value = user.Other;

            foreach (SqlParameter parm in UserInfoParms)
                cmd.Parameters.Add(parm);

            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_UPDATE_USERINFO_BY_USERID;
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }

        /// <summary>
        /// 获取家庭成员参数
        /// </summary>
        /// <returns></returns>
        private SqlParameter[] GetUserInfoParameters()
        {
            SqlParameter[] parms = new SqlParameter[] {
					new SqlParameter(FIELD_USERID, SqlDbType.VarChar, 18),
					new SqlParameter(FIELD_USERNAME, SqlDbType.VarChar, 50),
					new SqlParameter(FIELD_USERNAME2, SqlDbType.VarChar, 50),
					new SqlParameter(FIELD_SEX, SqlDbType.VarChar, 10),
					new SqlParameter(FIELD_HOMETOWN, SqlDbType.VarChar, 100),
					new SqlParameter(FIELD_ETHNICID, SqlDbType.VarChar, 128),
                    new SqlParameter(FIELD_POLITICALID, SqlDbType.VarChar, 128),
					new SqlParameter(FIELD_HEIGHT, SqlDbType.VarChar, 3),
					new SqlParameter(FIELD_WEIGHT, SqlDbType.VarChar, 3),
					new SqlParameter(FIELD_LEFTVISIONID, SqlDbType.VarChar, 128),
                    new SqlParameter(FIELD_RIGHTVISIONID, SqlDbType.VarChar, 128),
					new SqlParameter(FIELD_MARITAL, SqlDbType.VarChar, 10),
                    new SqlParameter(FIELD_BIRTHDAY, SqlDbType.VarChar, 20),
					new SqlParameter(FIELD_STARTWORKTIME, SqlDbType.DateTime),
					new SqlParameter(FIELD_JOB, SqlDbType.VarChar, 50),
					new SqlParameter(FIELD_SECTORPOSTS, SqlDbType.VarChar, 100),
					new SqlParameter(FIELD_LOCALTION, SqlDbType.VarChar, 50),
                    new SqlParameter(FIELD_PERSONNEFILES, SqlDbType.VarChar, 100),
					new SqlParameter(FIELD_PHONENUMBER, SqlDbType.VarChar, 50),
					new SqlParameter(FIELD_EMAIL, SqlDbType.VarChar, 50),
					new SqlParameter(FIELD_ADRESS, SqlDbType.VarChar, 100),
					new SqlParameter(FIELD_POSTALCODE, SqlDbType.VarChar, 6),
                    new SqlParameter(FIELD_TRAINING, SqlDbType.VarChar, 200),
					new SqlParameter(FIELD_PERFORMANCE, SqlDbType.VarChar, 200),
					new SqlParameter(FIELD_PHOTOADRESS, SqlDbType.VarChar, 128),
                    new SqlParameter(FIELD_OTHER, SqlDbType.VarChar, 200)
            };
            return parms;
        }
    }
}